nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report # 8 : - Cross Tab

nuraniuscc

Hi Mike,

I have a Cross Tab which will depend on the number of months as Input parms
But each column in the Cross Tab will have 2 columns of data, If you understand what I am saying.

Also, the 2 columns within each column has a Heading. How do I integrate
all this?

I need a quick and clear answer.

Thanks
Nurani Sivakumar

Find more posts tagged with

Comments

mwilliams

Hi Nurani,

You just need to apply the parameters to the dataSet. This will limit the data automatically to the data in the crosstab. For the 2 columns under a given main header (month), you just need to make those sub dimensions of the month in your dataCube I believe.

nuraniuscc

Hi Mike,

Yes, I have created a CROSSTAB. But each column will have 2 columns within it.

For Eg: (I am showing 1 column within the CROSSTAB)

February 2008

Actual Actual
vs vs
Starting Hours Ending Hours

(Click on the "EDIT" Button and you can see the formatting)

I know the CROSSTAB IS going to take care of the Month Part (Which would be a DataItem from the Datset)

How do I keep the 2nd Heading Line (Which is a Constant -Title) applied to
how many ever columns I might have (Depending on the Month Range)

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Can you post a small chunk of "fake" sample data in here how it would look in the dataSet and how you want it to look in the crosstab, so I can offer the best solution? Thanks.

nuraniuscc

Hi Mike,

I am sending the sample (fake) report in an email. Please refer to that.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

I was wanting some "fake" sample data, so I could try to create a report with it. This way, I know how your data is set up, so I can try to set up the crosstab how you'd have to set it up. Thanks.

nuraniuscc

Hi Mike,

I emailed you the report for this issue. I haven't heard anything on this. This is on the CrossTab and How to pull in the data into it?

FYI...I have created the Groups and Summary columns and I did align it to the columns that I am selecting. But when I run the report nothing happens.

Thanks
Nurani Sivakumar

PS : I am emailing the "Fake Report" again.

mwilliams

Nurani,

Can you send some "Fake data" to go with the report so I can run it? Thanks.

nuraniuscc

Hi Mike,

Here is the "Fake Data" that should come out from the SQL to be displayed on the report.

As, I said, I want the 2 Variance Hours data to be the 2 columns of the
Cross Tab.

Please click on the "Edit" button and You can see it nice and formatted.

Activity Cycle Bill Day Start Var Hrs End Var Hrs

Bill Approval M01 2 -15 30
Bill Approval M02 4 22 28
Bill Day Process M01 2 20 18
Bill Day Process M01 2 -2 5
Bills Mailed M01 2 -1 3

Hope that helps

Thanks
Nurani Sivakumar

mwilliams

Nurani,

After looking at this and re-going over the problem. I think I was making things too complicated. If you change the title for the 2 columns under the month, they will repeat like that for every month that the parameter allows in the crosstab. The crosstab takes care of all of that on its own.

nuraniuscc

Hi Mike,

So what exactly should I do? If you look at the Cross Tab, I have defined the
"Start Variance Hours" as a Group (Dimension) and the other one as a Measure (Summary).

Is that Right?

Could you please point to the exact location on the report where I need to make the changes?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

You need to make both of them Measures. Include both of them in the measures section in your datacube and crosstab and it'll repeat both columns for each month.

nuraniuscc

Hi Mike,

Yes, I did exactly what you said. I have both my SUM values as Measures and the Month as Groups. Good.

The report comes out and I see the cross tab for each month with different values. But, the same row gets repeated 10 times. All the records look identical.

I should get only 1 row but the crosstab will be for 10 months. But, Iam getting 10 rows each with 10 months.

Looks like some kind of CROSSTAB option that I need to use to Filter. Please
respond ASAP.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

If there is any way you can show me what the report looks like after generation, it would probably help.

nuraniuscc

Hi Mike,

Please refer to my email which has the .rptdesign and the fake report 8. This Cross Tab is challenging. Well, I did try what I understood so far. Please tell me exactly where and how to fix it or even better, if you modify and send back the .rptdesign that will be great.

Thanks
Nurani Sivakumar

nuraniuscc

Hi,

I would appreciate your quick response on this. I also sent the .rptdesign and the fake report to get the CROSSTAB working.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Sorry for the delay. I'll be looking at your report design here shortly. I'll let you know what I see.

mwilliams

Nurani,

I don't see a date in your dataSet. How do you sort out the February 2008, March 2008, etc? Also, the crosstab doesn't need to be inside another table unless you're grouping. You could have set the same thing up with a grid, but a table works fine too. You can probably just put the crosstab in a header or footer row though, the detail row is what is causing the repeat. If you can tell me how you figure out your month, I'll set up the datacube and crosstab to what I think is correct and send it back for you to try.

nuraniuscc

The Month comes from the SQL. The element is CYCLE which is the concatenation of Year and Month. For Eg : 200801. I need to convert them to read as "Jan 2008" (Which I haven't done). Anyways here is the SQL.

select B.ACTIVITYNAME,
A.DATACENTER,
A.CYCLEDAY,
A.RUNYEAR || A.RUNMONTH AS CYCLE,
floor(((ACTUALACTIVITYBEGIN - TARGETACTIVITYBEGIN) * 24 * 60 * 60)/3600) as Start_Var_Hours,
floor(((ACTUALACTIVITYEND - TARGETACTIVITYEND) * 24 * 60 * 60)/3600) as End_Var_Hours
from TBLTWCYCLERUN A,
TBLTWCYCLEDETAIL B
where A.DATACENTER = B.DATACENTER
and A.RUNYEAR = B.RUNYEAR
and A.RUNMONTH = B.RUNMONTH
and A.CYCLEDAY = B.CYCLEDAY

Thanks
Nurani Sivakumar

mwilliams

Nurani,

I set up the crosstab in your report design. I'll email it to you here after I submit this. Let me know if it works for you since I can't run it to test.

nuraniuscc

Hi Mike,

The Cross Tab is working. But How did you create it?
I need to know that, so I can mimic the same process for another report.

From what I understand,

Have you defined a CROSSTAB within a CROSS TAB?
The other 2 columns. i.e., DATACENTER and CYCLE DAY...they seem to be normal Dynamic Text fields. How did you manage to do that?

I also checked your DATACUBE and tried to define for another report DATACENTER within CYCLE. But when I try to DRAG AND DROP, I am unable to because the cursor is in protected mode.

Please explain how you got this clearly.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

This is just one datacube and one crosstab. I included the image of the crosstab cube builder to help me with my explanation.

Here is what I did to create this cube and crosstab:

* Drag a crosstab from the palette to the design where you want it.
* Drag a data field from the dataSet you want to use for your dataCube into the crosstab where you want it. (i.e. START_VAR_HOURS into the summary field area) This causes the dataCube editor to pop up with the field you already placed in the correct area.
* Drag any other summary fields, i.e. END_VAR_HOURS, to "drop a field here to create a summary".
* Drag the field that you want for your main row dimension and the field that you want for your main column dimension to where it says "drop a field here to create a group"
* If you want sub-dimensions under your row or column group, drag and drop the field from "available fields" onto the current group field. (i.e. Drop DATACENTER onto ACTIVITYNAME)
* Select OK
* Now you expand out your dataCube in the data explorer.
* Drag the main group field you want for the row dimension to the row dimension area of the crosstab in your design. If you have sub-dimensions you want to show, select the option button to the right of the data field, select show/hide group levels, and select the groups you want to show.
* Do the same for the column dimension area.
* Drag the summary fields from the dataCube to the summary area and you're done.

Let me know if you need any other information.

nuraniuscc

Mike,

While I understand how you did it, I did follow all the steps as you outlined, the one thing I want to know is "How did you get to define a cell inside a CROSSTAB" in which you can Drag and Drop the Database columns.

In the reprt that you sent back, DATACENTER and CYCLEDAY.

Because, I can't get to DRAG and DROP a Database column inside a CROSSTAB. That particular cell has to be created as a NON Crosstab type, if you know what I mean.

Also, How do I show a result data (which is an Integer from a computation) inside a CROSS TAB, Because it's not a COUNT and neither a SUM field (because if you drop it in the Summary area, it does a Sigma). Right?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

No, you cannot drag a data field from the dataSet to the crosstab. The only time I did that was just getting started. That links the dataCube to the dataSet and opens the dataCube editor. Any data field you actually put in the crosstab has to come from the dataCube, which all of mine did. If I'm not answering your question correctly, then I guess I'm not understanding the question correctly.

Let me know.

For the summary/measure, you can edit the expression and function that the aggregation uses after you set up the crosstab. Just double click on the aggregation item in the summary/measure area to open the editor for it.

nuraniuscc

Mike,

If you look at the .rptdesign that you sent me, You can see the DATACENTER and the ACTIVITY are NOT a CROSSTAB kind of field eventhough they all come under the CROSSTAB. You see the little image/icon on the Right of the field of a CROSSTAB (I don't know what you call them). But these 2 do not have them.

Because I want to follow your instructions and I do compare and contrast to what I want based on what you created.

Let me send you my new .rptdesign and the fake report for it and you can help me by making the changes (Like you did for the other one) and send it back to me.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Ok, I think I understand what you're talking about now. The reason the second two row dimensions aren't the same as the first one is because they are set up as sub groups of the first row dimension. If you look in the dataCube, you can see that the second two are under the first, not separate groups of their own.

Hope this helps.