Crosstab Report Works, but I need to add to it

Hi All,

I have worked hard and finally gotten my dynamic crosstab report working.  Now I would like to add data from a different record source.  

The crosstab report has dppm by model & category and month.  I need to add a input qty as another row, but it I can't put that in my crosstab query or it will create too many categories and mess up the look of the report.  If I made another crosstab query with the same months and used the input qty instead, how could I add that information to my report?
How it is now.How I want it to be...
LVL 1
Elena QuinnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
You question is similar to another question I just worked on.  In that question, the Asker was trying to get another row called BUSK added to his crosstab under the ModKit heading.  You can find the details on this link.

Basically, you would need to create a query similar to the query or table used as a source for your Crosstab query.  This new query should have the same number of columns but should have the Input Qty details you mentioned.  Also, the heading that normally would be the Category should be assigned the text "Usage."    

You would then create a Union Query using the original and new query and this would be used as the source for the crosstab instead.  

Hopefully you would be able to follow my explanation using the link I provided.  If not, it would be easiest if you upload a copy of your database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Elena QuinnAuthor Commented:
Your link seems to be broken.  It sends me to a list of questions and answers, but Q_28240080 is not one of them.  I will look into Union Queries, though and see if I can get there from here.  I'll post back either way.
0
Elena QuinnAuthor Commented:
Hi Again,

Love the Union idea.  It worked to get the usage data into the query in the same format.  So far so good.  However, in my report, I have a total row as well, only I don't want to add the usage row to that.  Any ideas how I might go about that?

Also, how can I make sure my categories are in a certain order?  I need the usage value at the very bottom, and the category 1 to be at the top, just below the totals.  The other categories don't really matter.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Elena QuinnAuthor Commented:
So, nevermind on the total thing.  I created yet another query for that and added it to the same union that feeds the report.  So now all the data is correct.  But it's still in the wrong order.  Still need help on that.
0
Elena QuinnAuthor Commented:
OK, I got it.  I went into the Sorting and Grouping in the report and was able to sort based on an expression like the below:

IIf([Category]="Gross DPPM",1,IIf([Category]="A",2,IIf([Category]="B",3,IIf([Category]="D",4,IIf([Category]="C",5,IIf([Category]="Usage",6,7))))))

Open in new window

Thought I would post this here in case others are looking for similar information.
0
Elena QuinnAuthor Commented:
Love the Union idea and it did what I wanted.
0
IrogSintaCommented:
Sorry about that broken link but great job in figuring the rest of this out!  
I also fixed the link in case anyone else happens upon this question.

Ron
0
IrogSintaCommented:
By the way, you can also use the Switch function in place of nested IIF statements.
Switch([Category]="Gross DPPM",1,[Category]="A",2,[Category]="B",3,[Category]="D",4,[Category]="C",5,[Category]="Usage",6)

Open in new window


Ron
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.